package org.yoao.moneymachine.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.yoao.crm.DBManager.DBManager;
import org.yoao.moneymachine.bean.User;

/**
 * @author Weishion.Yi
 * @description
 * @date 2010-10-4 下午03:12:13
 */
public class UserDAO extends DBManager {
	private static final Logger log = Logger.getLogger(UserDAO.class);

	public int getDataCount(Map map) throws Exception {
		String sql = "select count(id) as userCount from mmuser";
		String where = " where ";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				String value = map.get(key).toString();
				if (key.indexOf(" IN ") > 0) {
					where = where
							+ key
							+ "(select u3.userCode from mmuser as u3 group by u3.userCode having count(u3.userCode) > "
							+ value + ") and ";
				} else if (key.indexOf(" >= ") > -1) {
					where = where + key + value + " and ";
				} else
					where = where + key + "='" + value + "' and ";
			}
		}
		if (where.endsWith(" and "))
			where = where.substring(0, where.length() - 4);
		if (where.equals(" where "))
			where = "";
		sql += where;
		String result = querySingleValue(sql);
		if (result == null || result.trim().equals(""))
			result = "0";
		return Integer.parseInt(result);
	}

	public List find(int startRow, int endRow, Map map) throws Exception {
		String sql = "select top "
				+ endRow
				+ " mmuser.id, mmuser.userName, mmuser.email, mmuser.qq, mmuser.mobile, "
				+ "mmuser.platForm, mmuser.account, mmuser.type, mmuser.createTime, mmuser.amount, "
				+ "mmuser.passWord, mmuser.userCode, mmuser.expandCode, baseuser.nickName as userSno, "
				+ "mmuser.amountTotal, mmuser.amountTotalHistory from mmuser "
				+ "left join baseuser on baseuser.userName=mmuser.userSno "
				+ "where mmuser.id not in(";
		String filterSql = "select top " + startRow
				+ " u2.id from mmuser as u2 ";

		String where = "";
		String fwhere = "";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				String value = map.get(key).toString();
				if (key.indexOf(" IN ") > 0) {
					where = where
							+ "mmuser."
							+ key
							+ "(select u3.userCode from mmuser as u3 group by u3.userCode having count(u3.userCode) > "
							+ value + ") and ";
					fwhere = fwhere
							+ "u2."
							+ key
							+ "(select u3.userCode from mmuser as u3 group by u3.userCode having count(u3.userCode) > "
							+ value + ") and ";
				} else if (key.indexOf(" >= ") > -1) {
					where = where + key + value + " and ";
					fwhere = fwhere + key + value + " and ";
				} else {
					where = where + "mmuser." + key + "='" + value + "' and ";
					fwhere = fwhere + "u2." + key + "='" + value + "' and ";
				}
			}
		}
		if (where.endsWith(" and "))
			where = where.substring(0, where.length() - 4);
		if (where.equals(""))
			where = null;
		if (fwhere.endsWith(" and "))
			fwhere = fwhere.substring(0, fwhere.length() - 4);
		if (fwhere.equals(""))
			fwhere = null;

		if (where == null) {
			fwhere = " order by u2.id desc";
			sql = sql + filterSql + fwhere + ") order by mmuser.id desc";
		} else {
			filterSql = filterSql + " where " + fwhere + " order by u2.id desc";
			sql = sql + filterSql + ") and " + where
					+ " order by mmuser.id desc";
		}
		return find(sql);
	}

	public List findAll() throws Exception {
		String sql = "select  id, userName, passWord, email, qq, mobile, platForm, account, type, "
				+ "createTime, amount, userCode, expandCode from mmuser";
		return find(sql);
	}

	public User findById(String id) throws Exception {
		String sql = "select id, userName, passWord, email, qq, mobile, platForm, account, type, "
				+ "createTime, amount, userCode, expandCode from mmuser where id="
				+ id;
		return findSingle(querySingleRow(sql));
	}

	public User findByEmail(String email) throws Exception {
		String sql = "select id, userName, passWord, email, qq, mobile, platForm, account, type, "
				+ "createTime, amount, userCode, expandCode from mmuser where email='"
				+ email + "'";
		return findSingle(querySingleRow(sql));
	}

	public String findByUserCode(String userCode) throws Exception {
		String sql = "select COUNT(id) as idCount from mmuser where userCode='"
				+ userCode + "'";
		return querySingleValue(sql);
	}

	private List find(String sql) throws Exception {
		List list = queryMultiColumn(sql);
		List result = null;
		if (list != null && !list.isEmpty()) {
			result = new ArrayList();
			for (int i = 0; i < list.size(); i++) {
				Map map = (Map) list.get(i);
				User user = findSingle(map);
				if (user == null)
					user = new User();
				result.add(user);
			}
		}
		return result;
	}

	private User findSingle(Map data) throws Exception {
		User user = null;
		if (data != null && !data.isEmpty()) {
			user = new User();
			user.setId(data.get("ID").toString());
			user.setUserName((String) data.get("USERNAME"));
			user.setPassWord((String) data.get("PASSWORD"));
			user.setEmail((String) data.get("EMAIL"));
			user.setQq((String) data.get("QQ"));
			user.setMobile((String) data.get("MOBILE"));
			user.setPlatForm((String) data.get("PLATFORM"));
			user.setAccount((String) data.get("ACCOUNT"));
			user.setType((String) data.get("TYPE"));
			user.setCreateTime((String) data.get("CREATETIME"));
			user.setAmount((String) data.get("AMOUNT"));
			user.setUserCode((String) data.get("USERCODE"));
			user.setExpandCode((String) data.get("EXPANDCODE"));
			user.setUserSno((String) data.get("USERSNO"));
			String amountTotal = (String) data.get("AMOUNTTOTAL");
			if (amountTotal == null || amountTotal.trim().equals(""))
				amountTotal = "0";
			String amountTotalHistory = (String) data.get("AMOUNTTOTALHISTORY");
			if (amountTotalHistory == null
					|| amountTotalHistory.trim().equals(""))
				amountTotalHistory = "0";
			user.setAmountTotal(Float.parseFloat(amountTotal));
			user.setAmountTotalHistory(Float.parseFloat(amountTotalHistory));
		}
		return user;
	}

	public int insert(User user) throws Exception {
		String sql = "insert into mmuser(userName, passWord, email, qq, mobile, type, createTime, userCode, "
				+ "expandCode)values('"
				+ user.getUserName()
				+ "', '"
				+ user.getPassWord()
				+ "', '"
				+ user.getEmail()
				+ "', '"
				+ user.getQq()
				+ "', '"
				+ user.getMobile()
				+ "', '0', '"
				+ user.getCreateTime()
				+ "', '"
				+ user.getUserCode()
				+ "', '"
				+ user.getExpandCode() + "')";
		return update(sql);
	}

	public int modifyType(String id, String typeValue) throws Exception {
		String sql = "update mmuser set type='" + typeValue + "' ";
		if (id.indexOf(",") > 0)
			sql += "where id in(" + id + ")";
		else
			sql += "where id=" + id;
		System.out.println(sql);
		return update(sql);
	}

	public int modifyPayAmount(String id) throws Exception {
		String sql = "update mmuser set amountTotalHistory = amountTotalHistory + amountTotal, amountTotal=0 ";
		if (id.indexOf(",") > 0)
			sql += "where id in(" + id + ")";
		else
			sql += "where id=" + id;
		return update(sql);
	}

	public int modifyAmount(String id, String amountValue) throws Exception {
		String sql = "update mmuser set amount='" + amountValue + "' ";
		if (id.indexOf(",") > 0)
			sql += "where id in(" + id + ")";
		else
			sql += "where id=" + id;
		return update(sql);
	}

	public int modify(User user) throws Exception {
		String sql = "update mmuser set userName="
				+ setNull(user.getUserName()) + ", passWord="
				+ setNull(user.getPassWord()) + ", email="
				+ setNull(user.getEmail()) + ", qq=" + setNull(user.getQq())
				+ ", mobile=" + setNull(user.getMobile()) + ", platForm="
				+ setNull(user.getPlatForm()) + ", account="
				+ setNull(user.getAccount()) + ", type="
				+ setNull(user.getType()) + " where id=" + user.getId();

		return update(sql);
	}

	public int delete(String id) throws Exception {
		String sql = "delete from mmuser ";
		if (id.indexOf(",") > 0)
			sql += "where id in(" + id + ")";
		else
			sql += "where id=" + id;
		return update(sql);
	}
}